package cn.afterturn.easypoi.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.util.List;

/**
 * @author TcT
 *         Date: 2018/5/29.
 *         Time: 下午10:26.
 * @Title:
 * @Description:
 */
public class PoiPullDownUtil {


    /**
     * @param wb               HSSFWorkbook对象
     * @param realSheet        需要操作的sheet对象
     * @param datas            下拉的列表数据
     * @param startRow         开始行
     * @param endRow           结束行
     * @param startCol         开始列
     * @param endCol           结束列
     * @param hiddenSheetName  隐藏的sheet名
     * @param hiddenSheetIndex 隐藏的sheet索引
     * @return
     * @throws Exception
     */
    public static Workbook createDownList2003(Workbook wb, Sheet realSheet, List<String> datas, int startRow, int endRow,
                                                   int startCol, int endCol, String hiddenSheetName, int hiddenSheetIndex)
            throws Exception {

        HSSFWorkbook workbook = (HSSFWorkbook) wb;
        // 创建一个数据源sheet
        HSSFSheet hidden = workbook.createSheet(hiddenSheetName);
        // 数据源sheet页不显示
        workbook.setSheetHidden(hiddenSheetIndex, true);
        // 将下拉列表的数据放在数据源sheet上
        HSSFRow row = null;
        HSSFCell cell = null;
        //创建下拉列表的数据源
        for (int i = 0, length = datas.size(); i < length; i++) {
            row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(datas.get(i));
        }
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheetName + "!$A$1:$A" + datas.size());
        CellRangeAddressList addressList = null;
        HSSFDataValidation validation = null;
        row = null;
        cell = null;
        // 单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 循环指定单元格下拉数据
        for (int i = startRow; i <= endRow; i++) {
            row = (HSSFRow) realSheet.createRow(i);
            cell = row.createCell(startCol);
            cell.setCellStyle(style);
            addressList = new CellRangeAddressList(i, i, startCol, endCol);
            validation = new HSSFDataValidation(addressList, constraint);
            realSheet.addValidationData(validation);
        }
        return workbook;
    }


    /**
     * 2007版本,无下拉校验,建议用03版本做模板
     * @param wb
     * @param realSheet
     * @param datas
     * @param startRow
     * @param endRow
     * @param startCol
     * @param endCol
     * @param hiddenSheetName
     * @param hiddenSheetIndex
     * @return
     * @throws Exception
     */
    public static Workbook createDownList2007(Workbook wb, Sheet realSheet, List<String> datas, int startRow, int endRow,
                                                   int startCol, int endCol, String hiddenSheetName, int hiddenSheetIndex)
            throws Exception {

        XSSFWorkbook workbook = (XSSFWorkbook) wb;
        // 创建一个数据源sheet
        XSSFSheet hidden = workbook.createSheet(hiddenSheetName);
        // 数据源sheet页不显示
        workbook.setSheetHidden(hiddenSheetIndex, true);
        // 将下拉列表的数据放在数据源sheet上
        XSSFRow row ;
        XSSFCell cell;
        String[] strings = new String[datas.size()];
        //创建下拉列表的数据源
        for (int i = 0, length = datas.size(); i < length; i++) {
            row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(datas.get(i));
            strings[i] = datas.get(i);
        }
        XSSFDataValidationHelper validationHelper = new XSSFDataValidationHelper(hidden);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) validationHelper.createExplicitListConstraint(strings);
        CellRangeAddressList addressList ;
        XSSFDataValidation validation ;
        // 单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));//数据格式
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 循环指定单元格下拉数据
        for (int i = startRow; i <= endRow; i++) {
            row = (XSSFRow) realSheet.createRow(i);
            cell = row.createCell(startCol);
            cell.setCellStyle(style);
            addressList = new CellRangeAddressList(i, i, startCol, endCol);
            validation = (XSSFDataValidation) validationHelper.createValidation(dvConstraint, addressList);
            realSheet.addValidationData(validation);
        }
        return workbook;
    }
}
